package com.mtconnect.dao;

import com.mtconnect.exception.DAOException;
import com.mtconnect.exception.JDBCException;
import com.mtconnect.model.Condition;
import com.mtconnect.model.Event;
import com.mtconnect.model.Sample;
import com.mtconnect.util.JDBCTemplete;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * Created by Administrator on 2017/8/1.
 * <p>
 * 数据库初始化DAO层
 */
public class InitDB implements AutoCloseable{

    private static final Logger LOGGER = LoggerFactory.getLogger(InitDB.class);

    private JDBCTemplete jdbcTemplete = new JDBCTemplete();

    private EventMapper eventMapper = new EventMapper(jdbcTemplete);

    private ConditionMapper conditionMapper = new ConditionMapper(jdbcTemplete);

    private SampleMapper sampleMapper = new SampleMapper(jdbcTemplete);

    /**
     * create condition type table
     *
     * @param tableName    table name
     * @param sequenceName sequence name
     * @throws DAOException create prepare statement failed or close prepare statement error
     */
    public void createCondition(String tableName, String sequenceName) throws DAOException {
        LOGGER.debug("tableName is {},sequenceName is {}", tableName, sequenceName);
        String sql = "CREATE TABLE ? ('name' varchar(255) DEFAULT NULL, 'sequence' sequence(" + sequenceName + "), 'timestamp' TIMESTAMP(3) NOT NULL, dataItemID varchar(255) NOT NULL , subType varchar(255) default null, qualifier varchar(255) default null, nativeCode varchar(255) default null, nativeSeverity varchar(255) default null, statistic varchar(255) default null, warningType varchar(255) not null, 'value' varchar(255) default null)";
        PreparedStatement statement = null;
        try {
            statement = jdbcTemplete.getConnection().prepareStatement(sql);
            statement.setString(1, tableName);
            statement.execute();
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            LOGGER.debug("execute sql is ===> {}", sql);
            try {
                jdbcTemplete.close(null, statement, null);
            } catch (JDBCException e) {
                throw new DAOException(e);
            }
        }
    }

    /**
     * create event type table
     *
     * @param tableName    table name
     * @param sequenceName sequence name
     * @throws DAOException create prepare statement connection failed or close prepare statement connection error
     */
    public void createEvent(String tableName, String sequenceName) throws DAOException {
        LOGGER.debug("table name is {},sequence name is {}", tableName, sequenceName);

        String sql = "CREATE TABLE ? ('name' VARCHAR (255) DEFAULT NULL ,'sequence' SEQUENCE(" + sequenceName + "),'timestamp' TIMESTAMP(3) NOT NULL , 'dataItemID' VARCHAR (255) NOT NULL, 'subType' VARCHAR (255) DEFAULT NULL ,'assetType' VARCHAR (512) placeholder,'value' VARCHAR (255) DEFAULT 'UNAVAILABLE')";
        if ("ASSET_CHANGED".equalsIgnoreCase(tableName) || "ASSET_REMOVED".equalsIgnoreCase(tableName)) {
            sql = sql.replace("placeholder", "NOT NULL");
        } else {
            sql = sql.replace("placeholder", "DEFAULT NULL");
        }
        PreparedStatement statement = null;
        try {
            statement = jdbcTemplete.getConnection().prepareStatement(sql);
            statement.setString(1, tableName);
            statement.execute();
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            LOGGER.debug("execute sql is ===>{}", sql);
            try {
                jdbcTemplete.close(null, statement, null);
            } catch (JDBCException e) {
                throw new DAOException(e);
            }
        }
    }

    /**
     * create sample type talbe
     *
     * @param tableName    table name
     * @param sequenceName sequence name
     * @throws DAOException create prepare statement failed or close prepare statement error
     */
    public void createSample(String tableName, String sequenceName) throws DAOException {
        LOGGER.debug("param is : {} {}", tableName, sequenceName);
        String sql = "CREATE TABLE ? ('name' VARCHAR(255) DEFAULT NULL, 'sequence' sequence(" + sequenceName + "), 'timestamp' TIMESTAMP(3) NOT NULL , 'dataItemID' VARCHAR(255) NOT NULL, 'subType' VARCHAR(255) DEFAULT NULL, 'sampleRate' VARCHAR(255)  DEFAULT NULL, 'statistic' VARCHAR(32) DEFAULT NULL, 'duration' DECIMAL(38,38)  DEFAULT 0, 'sampleCount' DECIMAL(38,38)  DEFAULT 0, 'value' VARCHAR(255) DEFAULT 'UNAVAILABLE')";
        PreparedStatement statement = null;
        try {
            statement = jdbcTemplete.getConnection().prepareStatement(sql);
            statement.setString(1, tableName);
            statement.execute();
        } catch (SQLException e) {
            LOGGER.error("create sample table failed", e);
            throw new DAOException(e);
        } finally {
            try {
                jdbcTemplete.close(null, statement, null);
            } catch (JDBCException e) {
                throw new DAOException(e);
            }
        }
    }

    /**
     * 初始化数据
     *
     * @param tableName 表名称
     * @param sample    参数
     * @return 表中受影响的行数
     * @throws DAOException 一个数据库异常
     */
    public Integer insertSample(String tableName, Sample sample) throws DAOException {
        return sampleMapper.insertSample(tableName, sample);
    }

    public Integer insertEvent(String tableName, Event event) throws DAOException {
        return eventMapper.insertEvent(tableName, event);
    }

    public Integer insertCondition(String tableName, Condition condition) throws DAOException {
        return conditionMapper.insertCondition(tableName, condition);
    }

    public Condition getConditionByName(String tableName, String name) throws DAOException {
        LOGGER.debug("param is {} {}", tableName, name);
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String sql = "SELECT * FROM ? WHERE name = ?";
        Condition condition = null;
        try {
            statement = jdbcTemplete.getConnection().prepareStatement(sql);
            statement.setString(1, tableName);
            statement.setString(2, name);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                condition = new Condition();
                condition.setName(resultSet.getString(1));
                condition.setSequence(resultSet.getLong(2));
                condition.setTimestamp(resultSet.getTimestamp(3));
                condition.setDataItemID(resultSet.getString(4));
                condition.setSubType(resultSet.getString(5));
                condition.setQualifier(resultSet.getString(6));
                condition.setNativeCode(resultSet.getString(7));
                condition.setNativeSeverity(resultSet.getString(8));
                condition.setStatistic(resultSet.getString(9));
                condition.setWarningType(resultSet.getString(10));
                condition.setValue(resultSet.getString(11));
            }
            return condition;
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("query condition type table error", e);
            throw new DAOException(e);
        } finally {
            LOGGER.debug("execute sql is ===> {}", sql);
            try {
                jdbcTemplete.close(resultSet, statement, null);
            } catch (JDBCException e) {
                throw new DAOException(e);
            }
        }
    }

    /**
     * 统计某表中是否有数据。 根据dataItemID查询到相应的数据，在判断其subType是否变化。
     *
     * @param type       表名称（即元素的type属性）
     * @param subType    表中subType列的值（即元素的subType属性）
     * @param dataItemID 表中dataItemID列的值（即元素的id属性）
     * @return 1.如果这个元素（dataItemID）不存在且表中没有数据则返回 null
     * 2.返回0，表示subType被改变了。
     * 3.返回大于0，则表示这个条数据（以dataItemID列为准）并没变化
     * 4.返回小于0，则表示表中有数据，但是没有dataItemID对应的数据。
     */
    public Integer countData(String type, String subType, String dataItemID) throws DAOException {
        LOGGER.debug("param is type is {} ,subTypeBySample is {} ,dataItemID is {}", type, subType, dataItemID);
        String sql = "SELECT count(*) FROM '" + type + "' WHERE dataItemID = ?";
        PreparedStatement statement = null;
        Integer count = 0;
        try {
            statement = jdbcTemplete.getConnection().prepareStatement(sql);
            statement.setString(1, dataItemID);
            ResultSet var = statement.executeQuery();
            while (var.next()) {
                count = var.getInt(1);
            }
            var.close();
            statement.close();
            LOGGER.debug("dataItemID count is {}", count);
            if (count > 0) {//如果dataItemID存在
                if (subType == null) {//如果subType是空的
                    sql = "SELECT count(*) FROM '" + type + "' WHERE dataItemID = ? AND (subType IS NULL OR subType = '')";
                    statement = jdbcTemplete.getConnection().prepareStatement(sql);
                    statement.setString(1, dataItemID);
                    ResultSet set = statement.executeQuery();
                    while (set.next()) {
                        count = set.getInt(1);
                    }
                    set.close();
                    statement.close();
                    LOGGER.debug("subTypeBySample is null ,query count is {}", count);
                    return count;
                } else {//如果subType不是空
                    sql = "SELECT count(*) FROM '" + type + "' WHERE dataItemID = ? AND subType = ?";
                    statement = jdbcTemplete.getConnection().prepareStatement(sql);
                    statement.setString(1, dataItemID);
                    statement.setString(2, subType);
                    ResultSet set = statement.executeQuery();
                    while (set.next()) {
                        count = set.getInt(1);
                    }
                    set.close();
                    statement.close();
                    LOGGER.debug("subTypeBySample is {} ,query count is {}", subType, count);
                    return count;
                }
            } else {//dataItemID不存在，判断表中是否有数据
                sql = "SELECT count(*) FROM '" + type + "'";
                statement = jdbcTemplete.getConnection().prepareStatement(sql);
                ResultSet set = statement.executeQuery();
                while (set.next()) {
                    count = set.getInt(1);
                }
                set.close();
                statement.close();
                if (count > 0) {
                    return -1;
                }
                return null;
            }
        } catch (SQLException e) {
            LOGGER.error("DB query error", e);
            throw new DAOException(e);
        } finally {
            try {
                if (null != statement) {
                    statement.close();
                }
            } catch (SQLException e) {
                LOGGER.error("close statement error");
                throw new DAOException(e);
            }
        }
    }

    /**
     * 拿到数据库中所有的表
     *
     * @return 表名称集合
     */
    public List<String> getAllTable() throws DAOException {
        try {
            DatabaseMetaData metaData = jdbcTemplete.createConnection().getMetaData();
            String[] tables = {"TABLE"};
            ResultSet var = metaData.getTables(null, null, null, tables);
            List<String> list = new ArrayList<>();
            while (var.next()) {
                list.add(var.getString(3));
            }
            var.close();
            return list;
        } catch (SQLException e) {
            LOGGER.error("the DB connection error", e);
            throw new DAOException(e);
        }
    }

    /**
     * 拿到所有sequence的名称
     *
     * @return sequence名称集合
     */
    public List<String> getAllSequence() throws DAOException {
        String sql = "select seq_name from sys_sequence order by seq_name";
        try {
            List<String> var = new ArrayList<>();
            ResultSet resultSet = jdbcTemplete.executeQuery(sql);
            while (resultSet.next()) {
                var.add(resultSet.getString(1));
            }
            resultSet.close();
            return var;
        } catch (SQLException e) {
            LOGGER.error("take sequence name error", e);
            throw new DAOException(e);
        } finally {
            LOGGER.debug("execute sql is ===> {}", sql);
        }
    }

    /**
     * 创建sequence
     *
     * @param name sequence 的名字
     * @return 是否创建成功
     * @throws DAOException 关闭声明错误，执行语句错误抛出此异常
     */
    public void createSequence(String name) throws DAOException {
        LOGGER.debug("param is {}", name);
        String sql = "CREATE SEQUENCE '" + name + "' STARTVALUE 1";
        jdbcTemplete.executeSQL(sql);
        LOGGER.debug("execute sql is ===> {}", sql);
    }

    public Long getSequenceNum(String name) throws DAOException {
        String sql = "SELECT nextval('" + name + "') from dual";
        ResultSet resultSet = jdbcTemplete.executeQuery(sql);
        try {
            while (resultSet.next()) {
                return resultSet.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.warn("take result failed", e);
            throw new DAOException(e);
        } finally {
            try {
                jdbcTemplete.close(resultSet, null, null);
            } catch (JDBCException e) {
                LOGGER.warn("close resultSet error", e);
            }
        }
        return null;
    }

    public Connection getConnection(){
        return Optional.ofNullable(jdbcTemplete.getConnection()).get();
    }
    /**
     * 关闭数据库连接
     *
     */
    public void close() {
        try {
            Optional.ofNullable(jdbcTemplete.getConnection()).get().close();
        } catch (SQLException e) {
            LOGGER.error("Connection close error", e);
        }
    }
}
